drop table if exists jms_dws.dws_customer_hour_cnt_dt;
create external table jms_dws.dws_customer_hour_cnt_dt(
          input_date         string     comment  '录入日期'
         ,input_hour         string     comment  '录入小时'
         ,customer_name      string     comment  '客户名称'
         ,customer_code      string     comment  '客户code'
         ,attribute_id       string     comment  '客户属性'
         ,create_time        timestamp  comment  '客户签约年月'
         ,ticket_quantity    bigint     comment  '票量'
         ,franchisee_code    string     comment  '寄件加盟商code'
         ,franchisee_name    string     comment  '寄件加盟商名称'
         ,agent_code         string     comment  '寄件代理区code'
         ,agent_name         string     comment  '寄件代理区名称'
         ,provider_id        bigint     comment  '寄件省份id'
         ,city_id            bigint     comment  '寄件城市id'
         ,area_id            bigint     comment  '寄件区县id'
         ,provider_desc      string     comment  '寄件省份'
         ,city_desc          string     comment  '寄件城市'
         ,area_desc          string     comment  '寄件区县'
         ,pick_network_code  string     comment  '寄件网点code'
         ,pick_network_name  string     comment  '寄件网点名称'
 )
 comment '订单综合统计汇总表(小时)'
 partitioned by (dt string comment '更新/创建日期 (yyyy-mm-dd)')
 stored as parquet
 location '/dw/hive/jms_dws.db/external/dws_customer_hour_cnt_dt'
 tblproperties (
 'discover.partitions'='false',
 'parquet.column.index.access'='true'
 );

drop table if exists jms_dws.dws_customer_day_cnt_dt;
create external table jms_dws.dws_customer_day_cnt_dt(
          input_date         string     comment  '录入日期'
         ,customer_name      string     comment  '客户名称'
         ,customer_code      string     comment  '客户code'
         ,attribute_id       string     comment  '客户属性'
         ,customer_grade     string     comment  '客户等级'
         ,create_time        timestamp  comment  '客户签约年月'
         ,ticket_quantity    bigint     comment  '票量'
         ,franchisee_code    string     comment  '寄件加盟商code'
         ,franchisee_name    string     comment  '寄件加盟商名称'
         ,agent_code         string     comment  '寄件代理区code'
         ,agent_name         string     comment  '寄件代理区名称'
         ,provider_id        bigint     comment  '寄件省份id'
         ,city_id            bigint     comment  '寄件城市id'
         ,area_id            bigint     comment  '寄件区县id'
         ,provider_desc      string     comment  '寄件省份'
         ,city_desc          string     comment  '寄件城市'
         ,area_desc          string     comment  '寄件区县'
         ,pick_network_code  string     comment  '寄件网点code'
         ,pick_network_name  string     comment  '寄件网点名称'
 )
 comment '订单综合统计汇总表(天)'
 partitioned by (dt string comment '更新/创建日期 (yyyy-mm-dd)')
 stored as parquet
 location '/dw/hive/jms_dws.db/external/dws_customer_day_cnt_dt'
 tblproperties (
 'parquet.column.index.access'='true'
 );